package com.fbs.wowo.chat.mapper;

import com.fbs.wowo.chat.entity.Crew;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;

public interface CrewMapper {
    @Delete({
        "delete from crew",
        "where id = #{id,jdbcType=INTEGER}"
    })
    int deleteByPrimaryKey(Integer id);

    @Insert({
        "insert into crew (id, cid, ",
        "photo, num, uid, ",
        "name, pub, brief, ",
        "qrcode, valid, create_time, ",
        "update_time)",
        "values (#{id,jdbcType=INTEGER}, #{cid,jdbcType=VARCHAR}, ",
        "#{photo,jdbcType=VARCHAR}, #{num,jdbcType=INTEGER}, #{uid,jdbcType=VARCHAR}, ",
        "#{name,jdbcType=VARCHAR}, #{pub,jdbcType=TINYINT}, #{brief,jdbcType=VARCHAR}, ",
        "#{qrcode,jdbcType=VARCHAR}, #{valid,jdbcType=TINYINT}, #{createTime,jdbcType=TIMESTAMP}, ",
        "#{updateTime,jdbcType=TIMESTAMP})"
    })
    int insert(Crew record);

    @InsertProvider(type=CrewSqlProvider.class, method="insertSelective")
    int insertSelective(Crew record);

    @Select({
        "select",
        "id, cid, photo, num, uid, name, pub, brief, qrcode, valid, create_time, update_time",
        "from crew",
        "where id = #{id,jdbcType=INTEGER}"
    })
    @Results({
        @Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
        @Result(column="cid", property="cid", jdbcType=JdbcType.VARCHAR),
        @Result(column="photo", property="photo", jdbcType=JdbcType.VARCHAR),
        @Result(column="num", property="num", jdbcType=JdbcType.INTEGER),
        @Result(column="uid", property="uid", jdbcType=JdbcType.VARCHAR),
        @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
        @Result(column="pub", property="pub", jdbcType=JdbcType.TINYINT),
        @Result(column="brief", property="brief", jdbcType=JdbcType.VARCHAR),
        @Result(column="qrcode", property="qrcode", jdbcType=JdbcType.VARCHAR),
        @Result(column="valid", property="valid", jdbcType=JdbcType.TINYINT),
        @Result(column="create_time", property="createTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="update_time", property="updateTime", jdbcType=JdbcType.TIMESTAMP)
    })
    Crew selectByPrimaryKey(Integer id);

    @UpdateProvider(type=CrewSqlProvider.class, method="updateByPrimaryKeySelective")
    int updateByPrimaryKeySelective(Crew record);

    @Update({
        "update crew",
        "set cid = #{cid,jdbcType=VARCHAR},",
          "photo = #{photo,jdbcType=VARCHAR},",
          "num = #{num,jdbcType=INTEGER},",
          "uid = #{uid,jdbcType=VARCHAR},",
          "name = #{name,jdbcType=VARCHAR},",
          "pub = #{pub,jdbcType=TINYINT},",
          "brief = #{brief,jdbcType=VARCHAR},",
          "qrcode = #{qrcode,jdbcType=VARCHAR},",
          "valid = #{valid,jdbcType=TINYINT},",
          "create_time = #{createTime,jdbcType=TIMESTAMP},",
          "update_time = #{updateTime,jdbcType=TIMESTAMP}",
        "where id = #{id,jdbcType=INTEGER}"
    })
    int updateByPrimaryKey(Crew record);

    /**
     * 按群组id查找crew
     * @param cid
     * @return
     */
    @Select({
            "select",
            "id, cid, photo, num, uid, name, pub, brief, qrcode, valid, create_time, update_time",
            "from crew",
            "where cid = #{cid,jdbcType=INTEGER} and valid = 1"
    })
    @Results({
            @Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="cid", property="cid", jdbcType=JdbcType.VARCHAR),
            @Result(column="photo", property="photo", jdbcType=JdbcType.VARCHAR),
            @Result(column="num", property="num", jdbcType=JdbcType.INTEGER),
            @Result(column="uid", property="uid", jdbcType=JdbcType.VARCHAR),
            @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
            @Result(column="pub", property="pub", jdbcType=JdbcType.TINYINT),
            @Result(column="brief", property="brief", jdbcType=JdbcType.VARCHAR),
            @Result(column="qrcode", property="qrcode", jdbcType=JdbcType.VARCHAR),
            @Result(column="valid", property="valid", jdbcType=JdbcType.TINYINT),
            @Result(column="create_time", property="createTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="update_time", property="updateTime", jdbcType=JdbcType.TIMESTAMP)
    })
    Crew findByCid(String cid);

    /**
     * 用户加入的群组列表
     * 按用户id找中间表里的cid，再找crew表里的crew信息
     * @param uid
     * @return
     */
    @Select({
            "select c.id, c.cid, c.photo, c.num, c.uid, c.name, c.pub, c.brief, c.qrcode, c.valid, c.create_time, c.update_time",
            "from crew c",
            "inner join crew_member cm",
            "on c.cid = cm.cid",
            "where cm.uid = #{uid,jdbcType=VARCHAR} and cm.valid = 1"
    })
    @Results({
            @Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="cid", property="cid", jdbcType=JdbcType.VARCHAR),
            @Result(column="photo", property="photo", jdbcType=JdbcType.VARCHAR),
            @Result(column="num", property="num", jdbcType=JdbcType.INTEGER),
            @Result(column="uid", property="uid", jdbcType=JdbcType.VARCHAR),
            @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
            @Result(column="pub", property="pub", jdbcType=JdbcType.TINYINT),
            @Result(column="brief", property="brief", jdbcType=JdbcType.VARCHAR),
            @Result(column="qrcode", property="qrcode", jdbcType=JdbcType.VARCHAR),
            @Result(column="valid", property="valid", jdbcType=JdbcType.TINYINT),
            @Result(column="create_time", property="createTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="update_time", property="updateTime", jdbcType=JdbcType.TIMESTAMP)
    })
    List<Crew> crewList(String uid);

    @Select({
            "select",
            "id, cid, photo, num, uid, name, pub, brief, qrcode, valid, create_time, update_time",
            "from crew",
            "where valid = 1",
            "order by num desc"
    })
    @Results({
            @Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="cid", property="cid", jdbcType=JdbcType.VARCHAR),
            @Result(column="photo", property="photo", jdbcType=JdbcType.VARCHAR),
            @Result(column="num", property="num", jdbcType=JdbcType.INTEGER),
            @Result(column="uid", property="uid", jdbcType=JdbcType.VARCHAR),
            @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
            @Result(column="pub", property="pub", jdbcType=JdbcType.TINYINT),
            @Result(column="brief", property="brief", jdbcType=JdbcType.VARCHAR),
            @Result(column="qrcode", property="qrcode", jdbcType=JdbcType.VARCHAR),
            @Result(column="valid", property="valid", jdbcType=JdbcType.TINYINT),
            @Result(column="create_time", property="createTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="update_time", property="updateTime", jdbcType=JdbcType.TIMESTAMP)
    })
    List<Crew> findHot();

    @Select({
            "select",
            "id, cid, photo, num, uid, name, pub, brief, qrcode, valid, create_time, update_time",
            "from crew",
            "where name like \"%\"#{name,jdbcType=VARCHAR}\"%\" and valid = 1"
    })
    @Results({
            @Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="cid", property="cid", jdbcType=JdbcType.VARCHAR),
            @Result(column="photo", property="photo", jdbcType=JdbcType.VARCHAR),
            @Result(column="num", property="num", jdbcType=JdbcType.INTEGER),
            @Result(column="uid", property="uid", jdbcType=JdbcType.VARCHAR),
            @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
            @Result(column="pub", property="pub", jdbcType=JdbcType.TINYINT),
            @Result(column="brief", property="brief", jdbcType=JdbcType.VARCHAR),
            @Result(column="qrcode", property="qrcode", jdbcType=JdbcType.VARCHAR),
            @Result(column="valid", property="valid", jdbcType=JdbcType.TINYINT),
            @Result(column="create_time", property="createTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="update_time", property="updateTime", jdbcType=JdbcType.TIMESTAMP)
    })
    List<Crew> searchByName(String name);

    @Select({
            "select",
            "id, cid, photo, num, uid, name, pub, brief, qrcode, valid, create_time, update_time",
            "from crew",
            "where cid like \"%\"#{cid,jdbcType=VARCHAR}\"%\" and valid = 1"
    })
    @Results({
            @Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="cid", property="cid", jdbcType=JdbcType.VARCHAR),
            @Result(column="photo", property="photo", jdbcType=JdbcType.VARCHAR),
            @Result(column="num", property="num", jdbcType=JdbcType.INTEGER),
            @Result(column="uid", property="uid", jdbcType=JdbcType.VARCHAR),
            @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
            @Result(column="pub", property="pub", jdbcType=JdbcType.TINYINT),
            @Result(column="brief", property="brief", jdbcType=JdbcType.VARCHAR),
            @Result(column="qrcode", property="qrcode", jdbcType=JdbcType.VARCHAR),
            @Result(column="valid", property="valid", jdbcType=JdbcType.TINYINT),
            @Result(column="create_time", property="createTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="update_time", property="updateTime", jdbcType=JdbcType.TIMESTAMP)
    })
    List<Crew> searchById(String cid);
}